﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BI.CF.IDAL;
using BI.CF.Model;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace BI.CF.DAL
{
    public class UsersDAL : UsersIDAL
    {

        public static string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

        /// <summary>
        /// 会员列表显示
        /// </summary>
        /// <param name="UserName">用户名</param>
        /// <param name="UserrRealName">真实姓名</param>
        /// <param name="PageIndex">当前页</param>
        /// <param name="PageSize">每页显示条数</param>
        /// <returns></returns>
        PageList.Pager<UserrsModel> UsersIDAL.Show_Users(string UserName, string UserrRealName, int PageIndex, int PageSize)
        {
            SqlParameter[] parameter ={
                                         new SqlParameter("@UserName",SqlDbType.VarChar),
                                         new SqlParameter("@UserrRealName",SqlDbType.VarChar),
                                         new SqlParameter("@PageIndex",SqlDbType.Int),
                                         new SqlParameter("@PageSize",SqlDbType.Int)
                                     };
            parameter[0].Value = UserName;
            parameter[1].Value = UserrRealName;
            parameter[2].Value = PageIndex;
            parameter[3].Value = PageSize;
            SqlDataReader read = DbHelperSQL.RunProcedure("PROC_UserShow", parameter);
            List<UserrsModel> list = new List<UserrsModel>();
            while (read.Read())
            {
                UserrsModel m = new UserrsModel();
                m.UserId = Convert.ToInt32(read["UserId"].ToString());
                m.UserName = read["UserName"].ToString();
                m.HeadImage = read["HeadImage"].ToString();
                m.IdCard = read["IdCard"].ToString();
                m.UserSex =read["sex"].ToString();
                m.UserEmail = read["UserEmail"].ToString();
                m.UserPhone = read["UserPhone"].ToString();
                m.UserTel = read["UserTel"].ToString();
                m.MaritalState =read["MaritalState"].ToString();
                m.MIncome = Convert.ToDecimal(read["MIncome"].ToString());
                m.UserrBcardMesId =read["UserrBcardMesId"].ToString();
                m.UserrPlaceOrigin = read["UserrPlaceOrigin"].ToString();
                m.UserrAddres = read["UserrAddres"].ToString();
                m.UserrRealName = read["UserrRealName"].ToString();
                m.UserrOccupation = read["UserrOccupation"].ToString();
                m.UserAge = Convert.ToInt32(read["UserAge"].ToString());
                m.UserEducation = read["UserEducation"].ToString();
                m.Creditlimit = Convert.ToInt32(read["Creditlimit"].ToString());
                m.UserMemlevelId = Convert.ToInt32(read["UserMemlevelId"].ToString());
                list.Add(m);
            }
            PageList.Pager<UserrsModel> mlist = new PageList.Pager<UserrsModel>();
            mlist.Data = list;
            //一  连接数据源
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            //二  显示
            string sql = @"select UserId from  Users where 1=1";
            if (UserName != null && UserName != "")
            {
                sql += "and UserName LIKE '%" + UserName + "%' ";
            }
            if (UserrRealName != null && UserrRealName != "")
            {
                sql += "and UserName LIKE '% " + UserrRealName + " %' ";
            }
            SqlCommand cmd = new SqlCommand(sql, conn);//要执行的sql语句  选择数据源
            SqlDataReader sdr = cmd.ExecuteReader();//执行查询操作  返回一个读取器（多个值）
            ////读取器中的数据不能直接显示，必须通过Read方法来读
            //while (sdr.Read())//如果读取到数据 返回true 将读到的数据添加到控件
            //{
            //    comboBox1.Items.Add(sdr[0]);//将数据显示到控件中  sdr.GetString(0)  sdr["dname"]
            //}
            //sdr.Close(); //关闭读取器对象
            //三  关闭数据源
            int ii = 1;
            while (sdr.Read())
            {
                ii++;
            }
            conn.Close();
            ii -= 1;//总数量
            if (ii % PageSize == 0)
            {
                mlist.PageCount = ii / PageSize;
            }
            else
            {
                mlist.PageCount = (ii / PageSize) + 1;
            }
            mlist.PageIndex = PageIndex;
            mlist.PageSize = PageSize;
            return mlist;
        }

        /// <summary>
        /// 显示会员详情
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public UserrsModel Show_UserDetails(int UserID)
        {
            SqlParameter[] parameter ={
                                         new SqlParameter("@User_ID",SqlDbType.Int)
                                     };
            parameter[0].Value = UserID;
            SqlDataReader read = DbHelperSQL.RunProcedure("PROC_UserDetails", parameter);
            UserrsModel m = new UserrsModel();
            while (read.Read())
            {
                m.UserId = Convert.ToInt32(read["UserId"].ToString());
                m.UserName = read["UserName"].ToString();
                m.HeadImage = read["HeadImage"].ToString();
                m.IdCard = read["IdCard"].ToString();
                m.UserSex =read["UserSex"].ToString();
                m.UserEmail = read["UserEmail"].ToString();
                m.UserPhone = read["UserPhone"].ToString();
                m.UserTel = read["UserTel"].ToString();
                m.MaritalState =read["MaritalState"].ToString();
                m.MIncome = Convert.ToDecimal(read["MIncome"].ToString());
                m.UserrBcardMesId =read["UserrBcardMesId"].ToString();
                m.UserrPlaceOrigin = read["UserrPlaceOrigin"].ToString();
                m.UserrAddres = read["UserrAddres"].ToString();
                m.UserrRealName = read["UserrRealName"].ToString();
                m.UserrOccupation = read["UserrOccupation"].ToString();
                m.UserAge = Convert.ToInt32(read["UserAge"].ToString());
                m.UserEducation = read["UserEducation"].ToString();
                m.Creditlimit = Convert.ToInt32(read["Creditlimit"].ToString());
                m.UserMemlevelId = Convert.ToInt32(read["UserMemlevelId"].ToString());
            }
            return m;
        }

        /// <summary>
        /// 会员财产详情
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public MemPropertyModel Show_UserMemProperty(int UserID)
        {
            SqlParameter[] parameter ={
                                         new SqlParameter ("@UserID",SqlDbType.Int)
                                     };
            parameter[0].Value = UserID;
            SqlDataReader read = DbHelperSQL.RunProcedure("PROC_MemProperty_Show", parameter);
            MemPropertyModel m = new MemPropertyModel();
            while (read.Read())
            {
                m.MemPId = Convert.ToInt32(read["MemPId"].ToString());
                m.AccountTotal = Convert.ToDecimal(read["AccountTotal"]);
                m.FrozenTotal = Convert.ToDecimal(read["FrozenTotal"]);
                m.AvailableBalance = Convert.ToDecimal(read["AvailableBalance"]);
                m.IncomeSum = Convert.ToDecimal(read["IncomeSum"]);
                m.UserrId = Convert.ToInt32(read["UserId"].ToString());
            };
            return m;
        }
        /// <summary>
        /// 会员修改
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public int Users_GetUpdate(UserrsModel m)
        {
            SqlParameter[] parameter ={
                                         new SqlParameter("@HeadImage",SqlDbType.VarChar){Value=m.HeadImage},
                                         new SqlParameter("@UserName",SqlDbType.VarChar){Value=m.UserName},
                                         new SqlParameter("@IdCard",SqlDbType.VarChar){Value=m.IdCard},
                                         new SqlParameter("@UserSex",SqlDbType.Bit){Value=Convert.ToInt32( m.UserSex)},
                                         new SqlParameter("@UserEmail",SqlDbType.VarChar){Value=m.UserEmail},
                                         new SqlParameter("@UserPhone",SqlDbType.VarChar){Value=m.UserPhone},
                                         new SqlParameter("@UserTel",SqlDbType.VarChar){Value=m.UserTel},
                                         new SqlParameter("@MaritalState",SqlDbType.Bit){Value=Convert.ToInt32( m.MaritalState)},
                                         new SqlParameter("@MIncome",SqlDbType.Decimal){Value=m.MIncome},
                                         new SqlParameter("@UserrPlaceOrigin",SqlDbType.VarChar){Value=m.UserrPlaceOrigin},
                                         new SqlParameter("@UserrAddres",SqlDbType.VarChar){Value=m.UserrAddres},
                                         new SqlParameter("@UserrRealName",SqlDbType.VarChar){Value=m.UserrRealName},
                                         new SqlParameter("@UserrOccupation",SqlDbType.VarChar){Value=m.UserrOccupation},
                                         new SqlParameter("@UserAge",SqlDbType.Int){Value=m.UserAge},
                                         new SqlParameter("@UserEducation",SqlDbType.VarChar){Value=m.UserEducation},
                                         new SqlParameter("@Creditlimit",SqlDbType.VarChar){Value=m.Creditlimit},
                                         new SqlParameter("@UserMemlevelId",SqlDbType.Int){Value=m.UserMemlevelId},
                                         new SqlParameter("@UserId",SqlDbType.Int){Value=m.UserId}
                                     };
            bool i = DbHelperSQL.RunProcedureReturnBool("PROC_User_Update", parameter);
            if (i)
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }
    }
}
